USE [HomestayWebDB]
GO

/****** Object:  View [dbo].[Post_ResultSearch_View]    Script Date: 11/26/2012 18:44:24 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO







ALTER VIEW [dbo].[Post_ResultSearch_View]
AS
SELECT DISTINCT Posts.PostID
                ,Posts.TimeCreated
				,Posts.Title
				,Posts.IsAvailable
				,Province.RegionID AS ProvinceID
				,Province.Name AS Province
				,District.RegionID AS DistrictID
				,District.Name AS District
				,Posts.PostAvatar AS AvatarPost
				,Users.AvatarPicture AS AvatarUser			
				,Houses.PricePerDay
				,Houses.PricePerWeek
				,Houses.PricePerMonth
				,CAST(0 AS Decimal) AS PriceMin
				,CAST(0 AS Decimal) AS PriceMax
				,CAST(1 AS Int) AS PriceType		
				,Houses.Area
				,CAST (0 AS Float) AS AreaFrom
				,CAST (0 AS Float) AS AreaTo
				,Houses.AddressDetail AS AddressHouse				
				,TypeOfHouses.TypeOfHouseID AS TypeOfHousesID
				,TypeOfHouses.NAME AS TypeOfHousesNAME
				,HSType.HomeStayTypeID
				,HSType.HomeStayTypeName
				,CountLike.like_count
				,Users.Username
				,Houses.HasGara 
			    ,Houses.HasGarden 
			    ,Houses.HasKitchen
				
				
FROM Posts
	LEFT JOIN Houses
		ON (Posts.HouseID = Houses.HouseID)
	LEFT JOIN Regions AS Province
		ON (Province.RegionID = Houses.Province)
	LEFT JOIN Regions AS District
		ON (District.RegionID = Houses.District)
	LEFT JOIN TypeOfHouses
		ON  (Houses.TypeOfHouseID = TypeOfHouses.TypeOfHouseID)
	
	LEFT JOIN (SELECT COUNT(Likes.PostID) like_count, Likes.PostID
				FROM POSTS
					LEFT JOIN Likes ON (Posts.PostID = Likes.PostID)
				GROUP BY Likes.PostID) AS CountLike
		ON (Posts.PostID = CountLike.PostID)
	LEFT JOIN Users
		ON (Posts.Username = Users.Username)
	LEFT JOIN (SELECT Tourisms.HouseID, Tourisms.HomeStayTypeID, HomeStayTypes.Name AS HomeStayTypeName
				FROM Tourisms
					LEFT JOIN HomeStayTypes ON (Tourisms.HomeStayTypeID = HomeStayTypes.HomeStayTypeID)
						WHERE HomeStayTypes.IsDeleted = CAST (0 AS BIT))	AS HSType
		ON (Posts.HouseID = HSType.HouseID)
	
WHERE ISNULL(Posts.IsDeleted, CAST(0 AS BIT)) = CAST(0 AS BIT)
AND Users.IsDeleted = CAST (0 AS BIT)
	AND PostStatus = 1






GO


